﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Reflection;
using System.Data;

namespace SohuWeb.Models {
    public class DBUtil {
        SqlConnection _con;
        SqlCommand _cmd;
        SqlTransaction _trans;

        public DBUtil() {
            InitConnection();
        }

        private void InitConnection() {
            if (_con == null) {
                _con = new SqlConnection();
                _con.ConnectionString = "Server=cw99;Database=sohu;Uid=sa;Pwd=sa;";
            }
        }

        public IList<T> ExecuteReader<T>(string sql) {
            //InitConnection();
            _con.Open();
            try {
                _cmd = new SqlCommand(sql, _con);
                SqlDataReader sdr = _cmd.ExecuteReader();
                List<T> list = (List<T>)DataReader2Obj<T>(sdr);
                return list;
            } catch (Exception e) {
                Console.Out.WriteLine(e.Message);
            } finally {
                _con.Close();
            }
            return null;
        }

        public int ExecuteCmd(string sql) {
            
            _con.Open();
            try {
                _trans = _con.BeginTransaction(System.Data.IsolationLevel.ReadCommitted, "SQLTransaction");
                SqlCommand cmd = new SqlCommand(sql, _con);
                cmd.Transaction = _trans;
                int ret = cmd.ExecuteNonQuery();
                _trans.Commit();
                return ret;
            } catch (Exception e) {
                _trans.Rollback();
                Console.Out.Write(e.Message);
                return 0;
            } finally {
                _con.Close();
            }
        }

        public User GetUser(string username, string password) {
            string sql = string.Format("select * from s_user where username='{0}' and password='{1}'", username, password);
            List<User> list = (List<User>)ExecuteReader<User>(sql);
            if (list != null && list.Count > 0) {
                return list[0];
            }
            return null;
        }

        public List<User> GetAllUser() {
            return (List<User>)ExecuteReader<User>("select * from s_user order by uid");
        }

        public bool PutData(DataTable dTable) {
            _con.Open();
            try {
                _trans = _con.BeginTransaction(System.Data.IsolationLevel.ReadCommitted, "SQLTransaction");
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_con, SqlBulkCopyOptions.TableLock, _trans)) {
                    bulkCopy.DestinationTableName = "TempTable";
                    bulkCopy.BulkCopyTimeout = 60000;
                    bulkCopy.BatchSize = 0;
                    bulkCopy.WriteToServer(dTable);
                    bulkCopy.Close();
                }
                _trans.Commit();
            } catch {
                _trans.Rollback();
            } finally {
                _con.Close();
            }

            return true;
        }

        public bool ChangePassword(string username, string password) {
            string sql = string.Format("update s_user set password='{0}' where username='{1}'", password, username);
            return ExecuteCmd(sql) > 0;
        }

        public bool CreateUser(string username, string password, string memo, int level) {
            string sql = string.Format("insert into s_user(username,password,memo,level) values('{0}','{1}','{2}',{3})", username, password, memo, level);
            return ExecuteCmd(sql) > 0;
        }


        private IList<T> DataReader2Obj<T>(SqlDataReader rdr) {
            IList<T> list = new List<T>();
            while (rdr.Read()) {
                T t = System.Activator.CreateInstance<T>();
                Type obj = t.GetType();
                // 循环字段
                for (int i = 0; i < rdr.FieldCount; i++) {
                    object tempValue = null;
                    if (rdr.IsDBNull(i)) {
                        string typeFullName = obj.GetProperty(rdr.GetName(i)).PropertyType.FullName;

                        tempValue = GetDBNullValue(typeFullName);

                    } else {
                        tempValue = rdr.GetValue(i);

                    }
                    obj.GetProperty(rdr.GetName(i)).SetValue(t, tempValue, null);

                }
                list.Add(t);

            }
            return list;
        }
        private object GetDBNullValue(string typeFullName) {
            typeFullName = typeFullName.ToLower();
            if (typeFullName == "String") {
                return String.Empty;
            }
            if (typeFullName == "Int32") {
                return 0;
            }
            if (typeFullName == "DateTime") {
                return null;
                //return Convert.ToDateTime(BaseSet.DateTimeLongNull);
            }
            if (typeFullName == "Boolean") {
                return false;
            }
            if (typeFullName == "Int") {
                return 0;
            }
            return null;
        }
    }
}